package com.ecsolutions.service;

import com.ecsolutions.dao.Print_DAO;
import com.ecsolutions.entity.Print_entity;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.validation.groups.ConvertGroup;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * Created by ecs on 2017/8/4.
 */
@Service("Print_Service")
public class Print_ServiceImpl implements Print_Service {
    private Print_DAO print_dao;

    @Autowired
    public Print_ServiceImpl(Print_DAO print_dao) {this.print_dao = print_dao;}

    @Override
    public String printApplyEntity(Print_entity print_entity){
        //贷前信息
        String bpm_no = print_entity.getBpm_no();
        String custcod = print_entity.getCustcod();
        String startdate = print_dao.getStrdate(bpm_no);
        Print_entity UtilizationdetailInfo = print_dao.getUtilizationdetailInfo(bpm_no);
        String lineno = UtilizationdetailInfo.getLineno();
        Print_entity LoanApplicantInfo = print_dao.getLoanApplicantInfo(custcod);
        List<Print_entity> LoanapplicantfacilityinfoList = print_dao.getLoanapplicantfacilityinfo(custcod,lineno);
        List<Print_entity> loanList = print_dao.getLoanListInfo();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

        HSSFWorkbook wb = new HSSFWorkbook();//1.创建一个workbook，对应一个Excel文件
        HSSFSheet sheet = wb.createSheet("客户信息表");// 2.在workbook中添加一个sheet，对应Excel中的一个sheet
        for (int i = 0;i < 8;i++) {
            sheet.setColumnWidth(i, 8 * 512);
        }
        //设置单元格样式1
        CellStyle styleOne = wb.createCellStyle(); //创建样式对象1
        styleOne.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleOne.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置字体样式1
        HSSFFont font1 = wb.createFont();
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 20);//设置字体大小
        font1.setBold(true);//粗体显示
        styleOne.setFont(font1);
        //设置单元格样式2
        CellStyle styleTwo = wb.createCellStyle(); //创建样式对象2
        //设置字体样式2
        HSSFFont font2 = wb.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 11);//设置字体大小
        font2.setBold(true);//粗体显示
        styleTwo.setFont(font2);
        //设置单元格样式3
        CellStyle styleThree = wb.createCellStyle(); //创建样式对象3
        //设置字体样式3
        HSSFFont font3 = wb.createFont();
        font3.setFontName("宋体");
        font3.setFontHeightInPoints((short) 11);//设置字体大小
        styleThree.setFont(font3);

        //创建单元格在第一行
        Row row1 = sheet.createRow((short) 0);
        sheet.addMergedRegion(new CellRangeAddress(  0,0, 0, 7));// 设置单元格合并
        row1.createCell(0).setCellValue("审贷会摘要");
        for (int i = 0;i <= 7;i++) {
            Cell cell_temp = row1.getCell(i);
            if (cell_temp == null){
                cell_temp = row1.createCell(i);
            }
            cell_temp.setCellStyle(styleOne);//设置样式1
        }
        //创建单元格在第三行
        Row row3 = sheet.createRow((short) 2);
        row3.createCell(0).setCellValue("贷前流程号");
        row3.getCell(0).setCellStyle(styleTwo);
        row3.createCell(1).setCellValue(bpm_no);
        row3.getCell(1).setCellStyle(styleThree);
        //创建单元格在第四行
        Row row4 = sheet.createRow((short) 3);
        row4.createCell(0).setCellValue("额度号");
        row4.getCell(0).setCellStyle(styleThree);
        row4.createCell(1).setCellValue(UtilizationdetailInfo.getLineno());
        row4.getCell(1).setCellStyle(styleThree);
        //创建单元格在第五行
        Row row5 = sheet.createRow((short) 4);
        row5.createCell(0).setCellValue("贷款编号");
        row5.getCell(0).setCellStyle(styleThree);
        row5.createCell(1).setCellValue(UtilizationdetailInfo.getLoanref());
        row5.getCell(1).setCellStyle(styleThree);

        Row row6 = sheet.createRow((short) 5);
        row6.createCell(0).setCellValue("客户申请日期");
        row6.getCell(0).setCellStyle(styleThree);
        row6.createCell(1).setCellValue(startdate);
        row6.getCell(1).setCellStyle(styleThree);

        Row row7 = sheet.createRow((short) 6);
        row7.createCell(0).setCellValue("信贷员分析日期");
        row7.getCell(0).setCellStyle(styleThree);
        row7.createCell(1).setCellValue(UtilizationdetailInfo.getAnndate());
        row7.getCell(1).setCellStyle(styleThree);

        Row row8 = sheet.createRow((short) 7);
        row8.createCell(0).setCellValue("贷款目的");
        row8.getCell(0).setCellStyle(styleThree);
        row8.createCell(1).setCellValue(UtilizationdetailInfo.getDircdesc());
        row8.getCell(1).setCellStyle(styleThree);

        Row row10 = sheet.createRow((short) 9);
        row10.createCell(0).setCellValue("客户信息");
        row10.getCell(0).setCellStyle(styleTwo);

        Row row11 = sheet.createRow((short) 10);
        row11.createCell(0).setCellValue("客户号");
        row11.getCell(0).setCellStyle(styleThree);
        row11.createCell(1).setCellValue(LoanApplicantInfo.getCustcod());
        row11.getCell(1).setCellStyle(styleThree);

        Row row12 = sheet.createRow((short) 11);
        row12.createCell(0).setCellValue("客户名称");
        row12.getCell(0).setCellStyle(styleThree);
        row12.createCell(1).setCellValue(LoanApplicantInfo.getLastname());
        row12.getCell(1).setCellStyle(styleThree);

        Row row13 = sheet.createRow((short) 12);
        row13.createCell(0).setCellValue("地址");
        row13.getCell(0).setCellStyle(styleThree);
        row13.createCell(1).setCellValue(LoanApplicantInfo.getCommunicationAddress());
        row13.getCell(1).setCellStyle(styleThree);

        Row row14 = sheet.createRow((short) 13);
        row14.createCell(0).setCellValue("业务描述");
        row14.getCell(0).setCellStyle(styleThree);
        row14.createCell(1).setCellValue(LoanApplicantInfo.getBusinessscope());
        row14.getCell(1).setCellStyle(styleThree);

        Row row15 = sheet.createRow((short) 14);
        row15.createCell(0).setCellValue("商业经验");
        row15.getCell(0).setCellStyle(styleThree);
        row15.createCell(1).setCellValue("5个月");
        row15.getCell(1).setCellStyle(styleThree);

        Row row16 = sheet.createRow((short) 15);
        row16.createCell(0).setCellValue("雇员数量");
        row16.getCell(0).setCellStyle(styleThree);
        row16.createCell(1).setCellValue(LoanApplicantInfo.getLnofemp());
        row16.getCell(1).setCellStyle(styleThree);

        Row row17 = sheet.createRow((short) 16);
        row17.createCell(0).setCellValue("法定代表人");
        row17.getCell(0).setCellStyle(styleThree);
        row17.createCell(1).setCellValue(LoanApplicantInfo.getLegalrepresentativename());
        row17.getCell(1).setCellStyle(styleThree);

        if (loanList.size() != 0) {

            Row row19 = sheet.createRow((short) 18);
            row19.createCell(0).setCellValue("我行贷款");
            row19.getCell(0).setCellStyle(styleTwo);

            Row row20 = sheet.createRow((short) 19);
            row20.createCell(0).setCellValue("额度号");
            row20.getCell(0).setCellStyle(styleThree);
            row20.createCell(1).setCellValue("贷款编号");
            row20.getCell(1).setCellStyle(styleThree);
            row20.createCell(2).setCellValue("放款日期");
            row20.getCell(2).setCellStyle(styleThree);
            row20.createCell(3).setCellValue("放款金额");
            row20.getCell(3).setCellStyle(styleThree);
            row20.createCell(4).setCellValue("余额");
            row20.getCell(4).setCellStyle(styleThree);
            row20.createCell(5).setCellValue("到期日期");
            row20.getCell(5).setCellStyle(styleThree);
            row20.createCell(6).setCellValue("月利率（%）");
            row20.getCell(6).setCellStyle(styleThree);
            row20.createCell(7).setCellValue("是否逾期");
            row20.getCell(7).setCellStyle(styleThree);

            for (int i = 0; i < loanList.size() && i < LoanapplicantfacilityinfoList.size(); i++) {
                Row row20plus = sheet.createRow((short) (20 + i));
                row20plus.createCell(0).setCellValue(loanList.get(i).getLineno());
                row20plus.createCell(1).setCellValue(loanList.get(i).getLoanref());
                row20plus.createCell(2).setCellValue(loanList.get(i).getDrawdate());
                row20plus.createCell(3).setCellValue(loanList.get(i).getDrawamt());
                row20plus.createCell(4).setCellValue(LoanapplicantfacilityinfoList.get(i).getOsamt());
                row20plus.createCell(5).setCellValue(LoanapplicantfacilityinfoList.get(i).getExpirydate());
                row20plus.createCell(6).setCellValue(loanList.get(i).getSchemarate().toString());
                row20plus.createCell(7).setCellValue("");

                for (int j = 0; j < 7; j++) {
                    row20plus.getCell(j).setCellStyle(styleThree);
                }
            }

        }

        //文件保存在桌面
        try {
            FileOutputStream fileOut = new FileOutputStream("C:\\Users\\ecs\\Desktop\\客户信息表.xls");
            // 把上面创建的工作簿输出到文件中
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            return "文件已下载到桌面";
        }catch(IOException e){
            e.printStackTrace();
            return "文件下载失败";
        }
    }
}
